package model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import controller.DBmanager;


public class SignDao {

	private String sqlNewUser = "INSERT INTO USERS(username, password) VALUES(?,?)";
	private String sqlCheckUser = "SELECT * FROM USERS WHERE username=? AND password=?";
	private	String sqlSelectUser = "SELECT * FROM USERS WHERE userID=?";
	
	
	private Connection        con      = null;
	private PreparedStatement psCreate = null;
	private PreparedStatement psCheck = null;
	private PreparedStatement psSelect = null;
	
	public SignDao(){
		DBmanager myDb = DBmanager.getInstance();
		con = myDb.getConnection();
		try{
		    this.psCreate = con.prepareStatement(sqlNewUser);
		    this.psCheck = con.prepareStatement(sqlCheckUser);
		    this.psSelect = con.prepareStatement(sqlSelectUser);
		} catch(SQLException se) {
			printSQLException(se);
		}
	}
	
	public void newUser(String usern, String passwd){
		try {
			psCreate.clearParameters();
			psCreate.setString(1, usern);
			psCreate.setString(2, passwd);
			
			psCreate.executeUpdate();
		} catch (SQLException se) {
			printSQLException(se);
			newUser(usern, passwd);
		}
	}
	
	public int checkUser(String usern, String passwd){
		try{
			psCheck.clearParameters();
			psCheck.setString(1, usern);
			psCheck.setString(2, passwd);
			ResultSet rs = psCheck.executeQuery();
			while (rs.next()) {
				String username = rs.getString("username");
				String password = rs.getString("password");
				SignBean s = new SignBean(); 
				s.setAll(username, password) ;
				if(usern.equals(username)||passwd.equals(password)){
					int ret = rs.getInt("userID");
					rs.close() ;
					return ret;
				}
			}
		} catch(SQLException se) {
			printSQLException(se) ;
		}
		return 0;
	}

	private void printSQLException(SQLException se) {
		while(se != null) {
			se.printStackTrace();
			
			se = se.getNextException();
		}
	}
	
	public String getUsername(int id){
		try{
			psSelect.clearParameters();
			
			psSelect.setInt(1, id);
			ResultSet result = psSelect.executeQuery();
			if(result.next()){
				return result.getString("username");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}
